package controllers;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import play.mvc.Controller;


import models.Barco;
import models.Classe;
import models.Usuario;
import models.Competicao;;

public class visualizarBarcoCompeticao extends Application {

	private static Connection conexao;
	private static Statement comando;
	
	/*************************************************************/
	
	protected static void conectar() throws SQLException {  
	    
		  
		try {
			Class.forName("org.postgresql.Driver");
		} catch (ClassNotFoundException e) {
		}

			conexao = DriverManager.getConnection(  
			        "jdbc:postgresql://localhost:5432/SGRv2",  
			        "postgres",  
			        "senha");
		
			comando =  conexao.createStatement();

	}

	/***********************************************************/
	
	public static void visualizarBarcoCompeticao(int idcompeticao) throws SQLException {
		
		String competicaoid = "";
		try
		{		
			competicaoid  =  request.querystring.toString();

			competicaoid  = competicaoid .substring(5);
			idcompeticao = Integer.parseInt(competicaoid );
			
		}
		catch (Exception e)
		{
			
		}
		if(idcompeticao == 0)
		{
			visualizarCompeticoes.visualizarCompeticoes();			
		}
		else
		{
		Competicao competicao = new Competicao();
		
		competicao = Competicao.recuperarCompeticao(idcompeticao);

		List<Barco> barcosdisponiveis = new ArrayList<Barco>();
		List<Barco> barcosParticipantes = new ArrayList<Barco>();
			
		barcosdisponiveis =  Barco.recuperarBarcos("select * from barco where idbarco not in ( select idbarco from relacaobarcocompeticao where idcompeticao = "+ idcompeticao +")");
		
		barcosParticipantes = Barco.recuperarBarcos("select * from barco join relacaobarcocompeticao on barco.idbarco = relacaobarcocompeticao.idbarco where relacaobarcocompeticao.idcompeticao =" + idcompeticao );
		
		
		render(idcompeticao, competicao, barcosdisponiveis, barcosParticipantes );
		
		}
	}	

    /***************************************************************/
	
	public static void inserir(int idbarco, int idcompeticao) throws SQLException
	{
		if(idbarco > 0 && idcompeticao > 0)
		{
		conectar();
		
		comando.executeUpdate("insert into relacaobarcocompeticao (" +
		"idbarco, " +
        "idcompeticao "+
        
        ") values (" +
        "'" +idbarco + "'," +
        "'" +idcompeticao + "'" +
        
		")");
		
		visualizarBarcoCompeticao(idcompeticao);
		}
		else
		{
			visualizarCompeticoes.visualizarCompeticoes();		
		}
	}

		/***************************************************************/
		
	

		
}
